import os
import win32com.client

from DataAccess.blp_client import *

def extract_trade_from_ML(file_path):

	if os.path.isfile(file_path):
		file_name = os.path.split(file_path)[1]
		file_prefix = os.path.splitext(file_name)[0]
		file_suffix = os.path.splitext(file_name)[1]
		
		if file_prefix[:13].lower() == 'hkz_springsca' and  file_suffix[1:].lower() == 'xls':

			xApp = win32com.client.Dispatch("Excel.Application")
			xBook = xApp.Workbooks.Open(file_path)
			xSht = xBook.Worksheets('Standard')
			row = xSht.Range("A65536").End(win32com.client.constants.xlUp).Row + 1
			
			trade_rcd = []
			for i in range(1,row):
			
				if xSht.Cells(i, 1).Value == 'Side:':
					trade_type = xSht.Cells(i, 3).Value
					trade_type = trade_type.upper()
				if xSht.Cells(i, 11).Value == 'Trade Date':
					trade_date = xSht.Cells(i, 12).Value
				if xSht.Cells(i, 11).Value == 'Value Date':
					settle_date = xSht.Cells(i, 12).Value
				if xSht.Cells(i, 1).Value >=1 and xSht.Cells(i, 1).Value <=100:
					sedol = xSht.Cells(i, 3).Value + ' HK Equity'
					ticker = query_blp_data([sedol], ['TICKER_AND_EXCH_CODE'])[sedol]['TICKER_AND_EXCH_CODE'] + ' Equity'
					quantity = xSht.Cells(i, 6).Value
					cost_price = xSht.Cells(i, 11).Value/quantity
					local_price = xSht.Cells(i, 7).Value
					commission = xSht.Cells(i, 9).Value
					tax = xSht.Cells(i, 10).Value
					
					#print ticker
	
					trade_rcd.append([trade_date, ticker, trade_type, quantity, cost_price, 'HKD', 'STOCK', settle_date, 
								local_price, 'HKD', 1, commission, None, None, None, tax, 'ML'])
								
			xBook.Close()
			del xApp
			
			return trade_rcd
			
		else:
			raise RuntimeError() + 'File is not ML confirmation:' + file_name	
	else:
		raise RuntimeError() + 'File does not exist:' + file_path